<?php
include_once(realpath(dirname(__FILE__)).'/Dao.php');
include_once(realpath(dirname(__FILE__)).'/../Activity.php');
include_once(realpath(dirname(__FILE__)).'/../dao/UserDao.php');

class TeamDao extends Dao {
    private $_UNUSED_VALUE_ = 0;

    public function __construct() {
        parent::__construct();
    }

    /* inserts a team (input = Activity class)
     * outputs: $team->id is populated by the id as assigned by the database
                          iff it was 0 when passed into this fct
     * returns: on success: 1
     *          on error: 0
     */
    public function insert(&$team) {
        $tid = $team->getId(); //populated by DB
	$team_name = $team->getName();
	$sport = $team->getType();
	$level = $team->getLevel();
 	
        /* insert team */
        $sql = <<<SQL
INSERT INTO team VALUES
($tid,'$team_name','$sport', '$level')
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( $result!=1 ) die("Failed inserting into team table: ".mysql_error);

	/* get the tid of the thing we just added */
        $sql = <<<SQL
SELECT tid FROM team
ORDER BY tid DESC
LIMIT 1
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( $result==0 ) die("Failed to query tid of new team row: ".mysql_error);
	$tid = mysql_fetch_assoc($result);
	if( $tid==0 ) die("Failed to match tid of new team row: ".mysql_error);
	$tid = $tid['tid'];
	$team->setId($tid);

	/* insert captain into the user-team assoc. table */
	var_dump ( $team );
	var_dump ( $team->getCaptain() );
        $captain_uwid = $team->getCaptain()->getStudentId();
        $sql = <<<SQL
INSERT INTO map_team_account VALUES
('$tid'
,'$captain_uwid'
,1
)
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) die( mysql_error() );

	/* what to do with sport type..??? */
	/* ignore other members -- insertion only ever has captain as member */
    }
    

    /*
     * returns captain of a team
     */
    public function getCaptain($tid){

    	
    	$q= 'select uwid from map_team_account where isCaptain=1 AND tid=' . $tid;
    	$result = mysql_query($q , $this->m_cx);
    	//echo $q;
    	$r = mysql_fetch_assoc($result);
    	$UserDao = new UserDao();
    	return $UserDao->lookupByUwId($r['uwid']);
    
    }

    /* lookup teams that belong to user id
     * returns: on success: array of team IDs
     *          on error: 0
     */
    public function lookupTeamsByUwId($uwid) {
        $sql = <<<SQL
SELECT tid FROM map_team_account 
WHERE uwid = '$uwid'
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
	    echo "lookupTeamsByUwId: Failed to query for team association data\n";
            return 0;
        }
	$ret = array();
	while( $r = mysql_fetch_assoc($result) ) {
	    array_push($ret, $r['tid']);
	}
	return $ret;
    }
    
    /* removes a team (input = Activity class)
     * returns: on success: 1
     *          on error: 0
     */
    public function remove($team) {
        return 0; //Not implemented.
    }

    /* updates a team (input = Activity class)
     * returns: on success: 1
     *          on error: 0
     */
    public function update($team) {
         $tid = $team->getId();

         /* update team table - nothing to update */

	 /* update member/team assoc. table */
	 // 1. find those in sql but not in $team->members
	 $tid = $team->getStudentId();
         $sql = <<<SQL
SELECT uwid 
FROM map_team_account
WHERE tid = $tid
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if(!$result) die( mysql_error() );

	$to_delete = array();
	while( $r = mysql_fetch_assoc($result) ) {
	    if( in_array( $r['uwid'], $team->members ) ) {
		    array_push($to_delete, $r);
            }
	}

	// 2. delete those in sql but not in $team->members
	$in_string = implode(',', $to_delete);
        $sql = <<<SQL
DELETE FROM map_team_account 
WHERE uwid IN ( $in_string  )
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if(!$result) die( mysql_error() );

	// 3. add everyone in $team->members, ignoring duplicate keys
	// note the on duplicate update is invariant
	for( $ix = 0; $ix < count($team->members); ++$ix ) {
	    $uwid = $team->members->getStudentId();
	    $sql =+ <<<SQL
INSERT INTO map_team_account
VALUES( $tid, $ 
ON DUPLICATE UPDATE uwid = '$uwid'
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if(!$result) die( mysql_error() );

	}

	return 1;
    }

    /* adds a single member into a team
     * inputs: $team - object of class Activity
     *         $uwid - uw id of the team member to add
     * returns: on success: 1
     *          on error: 0
     */
    public function addTeammateByUwId($tid, $uwid, $isCaptain) {
	/* just a straight-up insert into the association table */
	   $sql = <<<SQL
INSERT INTO map_team_account VALUES
('$tid'
,'$uwid'
,'$isCaptain'
)
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
            echo "addTeammateByUwId: failed to write to table: ".mysql_error();
            return 0;
        }
        return 1;
    }

    /* look up a name by id
     * returns:  on fail: 0
     *           on success: name of the team
     *
     */
    public function lookupTeamName($team_id) {
        trim($team_id);
        $sql = <<<SQL
SELECT name,sport,skill
FROM team
WHERE tid='$team_id'
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
            echo "lookupTeamName: failed to lookup team name: ".mysql_error();
            return 0;
        }
        $r = mysql_fetch_assoc($result);
	if( !$r ) {
	    return 0;
	}
	return $r['name'];
    }

    /* look up a team by id 
     * inputs: $team_id: id of a team
     * returns: team object
     */
    public function lookupTeam($team_id) {
        /* look-up members */
        $member_id = $this->lookupMembers($team_id);
	for( $ix = 0; $ix < count($member_id); ++$ix ) {
	    $member_id[$ix] = "'".$member_id[$ix]."'";
	}
	$sql_in_str = implode(',', $member_id);

        $sql = <<<SQL
SELECT givenname, familyname, a.uwid, username, m.isCaptain
FROM account AS a
LEFT JOIN map_team_account AS m
ON a.uwid = m.uwid
  AND m.tid = $team_id
WHERE a.uwid IN ($sql_in_str)
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
            echo "lookupTeam: failed to lookup team members: ".mysql_error();
            return 0;
        }

        $captain = 0;
	$members = array();
        while( $r = mysql_fetch_assoc($result) ) {
            $m = new user($r['givenname']
	                 ,$r['familyname']
		         ,$r['uwid']
			 ,$r['username']
            );
	    array_push($members,$m);
	    if($r['isCaptain']==1) {
	        $captain = $m;
	    }
	}
	if( $captain == 0 ) {
	    /* sanity check */
	    echo "lookupTeam: failed to find captain.";
	    return 0;
	}

	/* look-up name */
        $sql = <<<SQL
SELECT name,sport,skill
FROM team
WHERE tid='$team_id'
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
            echo "lookupTeam: failed to lookup team name: ".mysql_error();
            return 0;
        }
        $r = mysql_fetch_assoc($result);
	if( !$r ) {
            echo "lookupTeam: team id does not exist: ".mysql_error();
	    return 0;
	}
	$tn = $r['name'];
	$sport = $r['sport'];
	$level = $r['skill'];

	/* construct */
        $ret = new Activity($team_id, $tn, $sport, $captain,$level);
	for( $ix = 0; $ix < count($members); ++$ix ) {
	    if( $captain->getStudentId() != $members[$ix]->getStudentId() ) {
	        $ret->addMember($members[$ix]);
            }
	}
	return $ret;
    }

    /* look up members of a team
     * inputs: 
     *         $team_id: id of team
     * returns: on success: array of uwids of team members
     *          on sql error: 0
     *          
     */
    public function lookupMembers($team_id) {
        /* look-up uwid of username */
        $sql = <<<SQL
SELECT uwid FROM map_team_account
WHERE tid = '$team_id'
SQL;
        $result = mysql_query($sql, $this->m_cx);
	if( !$result ) {
            echo "lookupMembers: failed to lookup team: ".mysql_error();
            return 0;
        }

	$ret = array();
        while( $r = mysql_fetch_assoc($result) ) {
	    array_push($ret, $r['uwid']);
	}
	return $ret;
    }

}//class
?>
